﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCCommon.Enums;
using BCCommon.PaymentEnums;
using BCEntity.Common.FlowRecord;
using Dapper;
namespace BCData.Common.FlowRecord
{
    public class UserFlowRecordData : IUserFlowRecordData
    {
        private const string QUERY = @"SELECT UserFlowRecordId,FlowType,OrderNo,FlowDirection,Amount,UserId,UserName,Channel,Remark,CreateTime,RiskLevel,Balance
                                        FROM UserFlowRecord";

        private readonly IDatabaseContext databaseContext;

        public UserFlowRecordData(IDatabaseContext databaseContext)
        {
            this.databaseContext=databaseContext;
        }

        public async Task<UserFlowRecordEntity> AddAsync(UserFlowRecordEntity entity)
        {
            var sql = SqlCommands.GetInsertSql("UserFlowRecord", new[] { "FlowType", "OrderNo", "Balance", "FlowDirection", "Amount", "UserId", "UserName", "Channel", "Remark", "RiskLevel" });
            sql = $"{sql} {QUERY}  WHERE UserFlowRecordId=LAST_INSERT_ID();";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FlowType", entity.FlowType.ToString());
            parameters.Add("?OrderNo", entity.OrderNo);
            parameters.Add("?Balance", entity.Balance);
            parameters.Add("?FlowDirection", entity.FlowDirection.ToString());
            parameters.Add("?Amount", entity.Amount);
            parameters.Add("?UserId", entity.UserId);
            parameters.Add("?UserName", entity.UserName);
            parameters.Add("?Channel", entity.Channel.ToString());
            parameters.Add("?Remark", entity.Remark);
            parameters.Add("?RiskLevel", entity.RiskLevel.ToString());

            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<UserFlowRecordEntity>(string.Format(sql, QUERY), parameters, databaseContext.Transaction);
        }

        public Task<UserFlowRecordEntity> Get(long flowRecordId)
        {
            string sql = @"{0} WHERE UserFlowRecordId=?UserFlowRecordId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserFlowRecordId", flowRecordId, DbType.Int64);
            return this.databaseContext.Slave.QueryFirstOrDefaultAsync<UserFlowRecordEntity>(string.Format(sql, QUERY), parameters);
        }

        public Task<UserFlowRecordEntity> Get(string orderNo, SourceFlowDirectionType flowDirectionType, FlowType flowType)
        {
            string sql = @"{0} WHERE FlowDirection=?FlowDirection AND FlowType=?FlowType AND OrderNo=?OrderNo;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?FlowDirection", flowDirectionType.ToString(), DbType.AnsiString);
            parameters.Add("?FlowType", flowType.ToString(), DbType.AnsiString);
            parameters.Add("?OrderNo", orderNo, DbType.AnsiString, size: 50);
            return this.databaseContext.Slave.QueryFirstOrDefaultAsync<UserFlowRecordEntity>(string.Format(sql, QUERY), parameters);
        }

        public async Task<(IEnumerable<UserFlowRecordEntity>, int)> GetFlowDetails(long? userId, bool? ascending, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (userId.HasValue)
            {
                wheres.Add("UserId=?UserId");
                parameters.Add("?UserId", userId, DbType.Int64);
            }
            string sql = string.Format(@"{0} {1} ORDER BY CreateTime {4} LIMIT {2},{3};
                               SELECT Count(1) FROM UserFlowRecord {1}",
                 QUERY,
                 wheres.Count > 0 ? string.Format(" WHERE {0}", string.Join(" AND ", wheres)) : string.Empty,
                 (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                 pageSize,
                 ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            return await this.databaseContext.Slave.QueryMultipleAsync<UserFlowRecordEntity, int>(sql, parameters);
        }

        public async Task<(IEnumerable<UserFlowRecordEntity>, int)> GetFlowDetails(long userId, DateTime? start, DateTime? end, 
            bool? ascending, int index, int size)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("UserId=?UserId");
            parameters.Add("?UserId", userId, DbType.Int64);
            if (start.HasValue)
            {
                wheres.Add("DATE(CreateTime) >= DATE(?Start)");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            if (end.HasValue)
            {
                wheres.Add("DATE(CreateTime) <= DATE(?End)");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY CreateTime {4} LIMIT {2},{3};
                               SELECT Count(1) FROM UserFlowRecord {1}",
                 QUERY,
                 wheres.Count > 0 ? string.Format(" WHERE {0}", string.Join(" AND ", wheres)) : string.Empty,
                 (index == 0 ? 0 : index - 1) * size,
                 size,
                 ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            return await this.databaseContext.Slave.QueryMultipleAsync<UserFlowRecordEntity, int>(sql, parameters);
        }

        public async Task<(IEnumerable<UserFlowRecordEntity>, int)> GetPageableList(long? userId, string orderNo, FlowType? flowType,
            SourceFlowDirectionType? flowDirection, PaymentChannelType? payChannel, DateTime? start, DateTime? end, bool? ascending, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (userId.HasValue)
            {
                wheres.Add("UserId=?UserId");
                parameters.Add("?UserId", userId, DbType.Int64);
            }
            if (!string.IsNullOrWhiteSpace(orderNo))
            {
                wheres.Add("OrderNo=?OrderNo");
                parameters.Add("?OrderNo", orderNo, DbType.AnsiString, size: 50);
            }
            if (flowType.HasValue)
            {
                wheres.Add("FlowType=?FlowType");
                parameters.Add("?FlowType", flowType.ToString(), DbType.AnsiString);
            }
            if (flowDirection.HasValue)
            {
                wheres.Add("FlowDirection=?FlowDirection");
                parameters.Add("?FlowDirection", flowDirection.ToString(), DbType.AnsiString);
            }
            if (payChannel.HasValue)
            {
                wheres.Add("Channel=?Channel");
                parameters.Add("?Channel", payChannel.ToString(), DbType.AnsiString);
            }

            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY UserFlowRecordId {4} LIMIT {2},{3};
                               SELECT Count(1) FROM UserFlowRecord {1}",
                 QUERY,
                 wheres.Count > 0 ? string.Format(" WHERE {0}", string.Join(" AND ", wheres)) : string.Empty,
                 (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                 pageSize,
                 ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            return await this.databaseContext.Slave.QueryMultipleAsync<UserFlowRecordEntity, int>(sql, parameters);
        }

        //public async Task<decimal> GetDriverPaymentAccountBalance(string driverName, string phone, string vehicleNo, DateTime? startDate, DateTime? endDate)
        //{
        //    List<string> wheres = new List<string>();
        //    DynamicParameters parameters = new DynamicParameters();
        //    wheres.Add("A.Channel = ?Channel");
        //    parameters.Add("?Channel", PaymentChannelType.Balance.ToString(), DbType.AnsiString);
        //    wheres.Add("A.FlowDirection = ?FlowDirection");
        //    parameters.Add("?FlowDirection", SourceFlowDirectionType.Outlay.ToString(), DbType.AnsiString);
        //    if (!string.IsNullOrWhiteSpace(driverName))
        //    {
        //        wheres.Add("A.UserName = ?UserName");
        //        parameters.Add("?UserName", driverName, DbType.AnsiString);
        //    }
        //    if (!string.IsNullOrWhiteSpace(phone))
        //    {
        //        wheres.Add("B.Phone = ?Phone");
        //        parameters.Add("?Phone", phone, DbType.AnsiString);
        //    }
        //    if (!string.IsNullOrWhiteSpace(vehicleNo))
        //    {
        //        wheres.Add("D.VehicleNo = ?VehicleNo");
        //        parameters.Add("?VehicleNo", vehicleNo, DbType.AnsiString);
        //    }
        //    if (startDate.HasValue)
        //    {
        //        wheres.Add("A.CreateTime >= ?StartDate");
        //        parameters.Add("?StartDate", startDate.Value.Date, DbType.Date);
        //    }
        //    if (endDate.HasValue)
        //    {
        //        wheres.Add("A.CreateTime < ?EndDate");
        //        parameters.Add("?EndDate", endDate.Value.AddDays(1).Date, DbType.Date);
        //    }
        //    string sql = string.Format(@"SELECT SUM(ABS(A.Amount)) 
        //                FROM UserFlowRecord A
        //                INNER JOIN `User` B ON A.UserId = B.UserId AND B.Role = 'Driver'
        //                INNER JOIN Driver C ON B.UserId = C.UserId 
        //                INNER JOIN Vehicle D ON C.DriverId = D.DriverId 
        //                {0} ",
        //                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
        //    return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<decimal>(sql, parameters);
        //}

        //public async Task<(IEnumerable<FreightFinanceAccountReferenceBalanceTransformationEntity>, int)> GetList(string driverName, string phone, string vehicleNo, DateTime? start, DateTime? end, int? pageIndex, int? pageSize)
        //{
        //    List<string> wheres = new List<string>();
        //    DynamicParameters parameters = new DynamicParameters();
        //    wheres.Add("A.Channel = ?Channel");
        //    parameters.Add("?Channel", PaymentChannelType.Balance.ToString(), DbType.AnsiString);
        //    wheres.Add("A.FlowDirection = ?FlowDirection");
        //    parameters.Add("?FlowDirection", SourceFlowDirectionType.Outlay.ToString(), DbType.AnsiString);
        //    if (!string.IsNullOrWhiteSpace(driverName))
        //    {
        //        wheres.Add("A.UserName = ?UserName");
        //        parameters.Add("?UserName", driverName, DbType.AnsiString);
        //    }
        //    if (!string.IsNullOrWhiteSpace(phone))
        //    {
        //        wheres.Add("B.Phone = ?Phone");
        //        parameters.Add("?Phone", phone, DbType.AnsiString);
        //    }
        //    if (!string.IsNullOrWhiteSpace(vehicleNo))
        //    {
        //        wheres.Add("D.VehicleNo = ?VehicleNo");
        //        parameters.Add("?VehicleNo", vehicleNo, DbType.AnsiString);
        //    }
        //    if (start.HasValue)
        //    {
        //        wheres.Add("A.CreateTime >= ?StartDate");
        //        parameters.Add("?StartDate", start.Value.Date, DbType.Date);
        //    }
        //    if (end.HasValue)
        //    {
        //        wheres.Add("A.CreateTime < ?EndDate");
        //        parameters.Add("?EndDate", end.Value.AddDays(1).Date, DbType.Date);
        //    }
        //    string sql = string.Format(@"SELECT A.Channel AS AccountType,A.Balance,A.CreateTime,E.PaymentRecordId,
        //                E.PaymentChannelId,E.OrderType,E.ReferenceNumber,E.Amount,A.UserId,A.UserName AS DriverName,
        //                B.Phone,E.`Status`,E.CreateTime AS PaymentRecordCreateTime,E.SuccessTime,D.VehicleNo 
        //                FROM UserFlowRecord A 
        //                INNER JOIN `User` B ON A.UserId = B.UserId AND B.Role = 'Driver'
        //                INNER JOIN Driver C ON B.UserId = C.UserId 
        //                INNER JOIN Vehicle D ON C.DriverId = D.DriverId
        //                INNER JOIN PaymentRecord E ON A.OrderNo = E.ReferenceNumber AND E.PaymentChannelId = 'Balance' AND E.`Status` = 'Success'
        //                {0} ORDER BY A.CreateTime DESC LIMIT {1},{2};
        //                SELECT COUNT(1) 
        //                FROM UserFlowRecord A 
        //                INNER JOIN `User` B ON A.UserId = B.UserId AND B.Role = 'Driver'
        //                INNER JOIN Driver C ON B.UserId = C.UserId 
        //                INNER JOIN Vehicle D ON C.DriverId = D.DriverId
        //                INNER JOIN PaymentRecord E ON A.OrderNo = E.ReferenceNumber AND E.PaymentChannelId = 'Balance' AND E.`Status` = 'Success'
        //                {0}",
        //                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
        //                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
        //                pageSize);
        //    return await this.databaseContext.Slave.QueryMultipleAsync<FreightFinanceAccountReferenceBalanceTransformationEntity, int>(sql, parameters);
        //}
    }
}